/* MonthStorage.java
 *
 * Project/Product: GW
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   BENNY	2008-11-4	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2008
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package system.base;
import java.sql.*;

import com.apep.lib.xml.SampleElement;

import framework.core.DBConnection;
import framework.core.DBConnectionManager;
import framework.core.DBEngine;

/** 月结操作    */
public class MonthStorageHis {
    private String timeStart,timeEnd,createUserId,createUser,createTime,lastUpdateUserId,lastUpdateUser,
        warehouseCode,balanceUser,balanceUserId,month;
    private String status = "1";
    private String isSync = "0";
    private String balanceFlag = "1";
    public static DBConnection dbConn = null;
    private static boolean isRunning = false;
    
    /*  构造器 注入  网上结算所需的参数*/
    /**
     *@param String timeStart = "2008-10-01";
     *@param String timeEnd = "2008-10-31 23:59:59";
     *@param String createUserId
     *@param String createUser,
     *@param String createTime = "2008-10-20 23:59:59";
     *@param String lastUpdateUserId,
     *@param String lastUpdateUser,
     *@param String warehouseCode = "000301";,
     *@param String month = "200810";*/
    public MonthStorageHis(String timeStart,String timeEnd,String createUserId,String createUser,String createTime,
            String lastUpdateUserId,String lastUpdateUser,String warehouseCode,String month){
        this.timeStart = timeStart;
        this.timeEnd = timeEnd;
        this.createUserId = createUserId;
        this.createUser = createUser;
        this.createTime = createTime;
        this.lastUpdateUserId = lastUpdateUserId;
        this.lastUpdateUser = lastUpdateUser;
        this.warehouseCode = warehouseCode;
        this.month = month;
    }

    public static boolean balance(SampleElement se){
        isRunning = true;
        String timeStart,timeEnd,createUserId,createUser,createTime,
            lastUpdateUserId,lastUpdateUser,warehouseCode,month;
        MonthStorageHis ms = null;
        try {
            timeStart = se.getChildElement("product_count").getTagValue();
            timeEnd = se.getChildElement("product_money").getTagValue();
            createUserId = se.getChildElement("create_user_id").getTagValue();
            createUser = se.getChildElement("create_user").getTagValue();
            createTime = se.getChildElement("create_time").getTagValue();
            lastUpdateUserId = se.getChildElement("last_update_user_id").getTagValue();
            lastUpdateUser = se.getChildElement("last_update_user").getTagValue();
            warehouseCode = se.getChildElement("warehouse_code").getTagValue();
            month = se.getChildElement("month").getTagValue();
            ms = new MonthStorageHis(timeStart,timeEnd,createUserId,createUser,createTime,
                    lastUpdateUserId,lastUpdateUser,warehouseCode,month);
        } catch (RuntimeException e) {
            e.printStackTrace();
            return false;
        }
        boolean result = ms.balance();
        isRunning = false;
        return result;
    }
    
    
    public boolean balance(){
        //获得手动事务控制的Connection
        Connection con = null;
        con = this.getConnection(false);
        boolean result = false;
        result = this.balanceMonthStorage(con);
        if(result)
            result = this.balanceInHead(con);
        if(result)
            result = this.balanceOutHead(con);
        try {
            if(result){
                con.commit();
            }
            else{
                con.rollback();
            }
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally{
            if(con != null){
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(dbConn != null){
                try {
                    dbConn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    /*  获得APEP  框架  DBConnection    提供的Connection  使用后请关闭 MonthStorage.dbConn*/
    private Connection getConnection(boolean autoCommit){
        Connection con = null;
        try {
            if(dbConn == null || dbConn.isClosed()){
                dbConn = DBConnection.open(false);
                con = dbConn.getConnection();
                con.setAutoCommit(autoCommit);
            }else{
                con = dbConn.getConnection();
                con.setAutoCommit(autoCommit);
            }
            return con;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        
    }
    /*  结算 库存*/
    private boolean balanceMonthStorage(Connection con){
        String sql = 
    "insert into t_month_storage (month,product_storage_id,warehouse_code,warehouse_name,position_code,position_name,product_id,product_code,product_count,product_money,status,create_user_id,create_user,create_time,last_update_user_id,last_update_user,last_update_time,is_sync,balance_mode,provider_code)"+
        " select"+                                                                                                                                                                              
            " '"+this.month+"' month,"+                                                                   
            " ps.id product_storage_id,"+                                                                 
            " ps.warehouse_code warehosue_code,"+                                                         
            " ps.warehouse_name warehouse_name,"+                                                         
            " ps.position_code position_code,"+                                                           
            " pos.name position_name,"+                                                           
            " ps.product_id product_id,"+                                                                 
            " ps.product_code product_code,"+                                                             
            " finalresult.acount product_count,"+                                                         
            " finalresult.amoney product_money,"+                                                         
            " '"+this.status+"' status,"+                                                                 
            " '"+this.createUserId+"' create_user_id,"+                                                   
            " '"+this.createUser+"' create_user,"+                                                        
            " '"+this.createTime+"' create_time,"+                                                        
            " '"+this.lastUpdateUserId+"' last_update_user_id,"+                                          
            " '"+this.lastUpdateUser+"' last_update_user,"+                                               
            " (select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual) last_update_time,"+             
            " '"+this.isSync+"' is_sync,"+                                                                
            " finalresult.balmode,"+                                                                      
            " finalresult.prvcode"+                                                                       
        " from("+                                                                                         
            " select"+                                                                                    
                    " product_id,"+                                                                       
                    " procode,"+                                                                          
                    " poscode,"+                                                                          
                    " sum(acount) acount,"+                                                               
                    " sum(amoney) amoney,"+                                                               
                    " prvcode,"+                                                                          
                    " balmode,"+                                                                          
                    " price"+                                                                             
            " from ("+                                                                                    
                " select"+                                                                                
                    " ps.product_id,"+                                                                    
                    " ps.product_code procode,"+                                                          
                    " ps.position_code poscode,"+                                                         
                    " sum(ps.product_count) acount,"+                                                     
                    " sum(ps.product_money) amoney,"+                                                     
                    " ps.provider_code prvcode,"+                                                         
                    " ps.balance_mode balmode,"+                                                          
                    " ps.product_price price"+                                                            
                " from"+                                                                                  
                    " t_product_storage ps"+                                                              
                " where"+                                                                                 
                    " ps.warehouse_code = '"+this.warehouseCode+"'"+                                      
                " group by"+      
                        " ps.product_id,"+                                                                     
                    " ps.product_code,"+                                                                  
                    " ps.position_code,"+                                                                 
                    " ps.provider_code,"+                                                                 
                    " ps.balance_mode,"+                                                                  
                    " ps.product_price"+                                                                  
                " union all"+                                                                             
                " select"+                                                                                
                    " sub.product_id,"+                                                                   
                    " sub.product_code procode,"+                                                         
                    " sub.position_code poscode,"+                                                        
                    " sum(sub.product_count) * -1 as acount,"+                                                 
                    " sum(sub.product_money) * -1 as amoney,"+                                                 
                    " head.provider_code prvcode,"+                                                       
                    " sub.balance_mode balmode,"+                                                         
                    " sub.product_price price"+                                                           
                " from"+                                                                                  
                        " t_in_sub sub"+                                                                                                                           
                        " left outer join t_in_head head"+                                                                                                     
                            " on  sub.in_bill_code = head.in_bill_code"+                                                
                " where"+                                                                                  
                    " sub.status = '1'"+                                                              
                    " and head.in_time > '"+this.timeEnd+"'"+                                             
                    " and head.warehouse_code = '"+this.warehouseCode+"'"+                                
                " group by"+                                                                                                                           
                        " sub.product_id,"+                                                                             
                    " sub.product_code,"+                                                                 
                    " sub.position_code,"+                                                                
                    " head.provider_code,"+                                                               
                    " sub.balance_mode,"+                                                                 
                    " sub.product_price"+                                                                 
                " union all"+                                                                             
                " select"+                                                                                
                    " sub.product_id,"+                                                                   
                    " sub.product_code procode,"+                                                         
                    " sub.position_code poscode,"+                                                        
                    " sum(sub.product_count) as acount,"+                                            
                    " sum(sub.product_money) as amoney,"+                                            
                    " sub.provider_code prvcode,"+                                                        
                    " sub.balance_mode balmode,"+                                                         
                    " sub.product_price price"+                                                           
                " from"+                                                                                                                   
                    " t_out_sub sub"+                                                                                                                          
                        " left outer join t_out_head head"+                                                                                                                                                                                                                              
                            " on  sub.out_bill_code = head.out_bill_code"+                                                                                                                                                                               
                " where"+                                                                                 
                    " sub.status = '1'"+                                                              
                    " and head.out_time > '"+this.timeEnd+"'"+                                            
                    " and head.warehouse_code = '"+this.warehouseCode+"'"+                                
                " group by"+                                                                                                                             
                        " sub.product_id,"+                                                                 
                    " sub.product_code,"+                                                                 
                    " sub.position_code,"+                                                                
                    " sub.provider_code,"+                                                                
                    " sub.balance_mode,"+                                                                 
                    " sub.product_price"+                                                                 
            " ) result"+                                                                                  
            " group by" +                                                                                                                 
                        " product_id,"+                                                                                
                " procode," +                                                                             
                " poscode," +                                                                             
                " prvcode," +                                                                             
                " balmode," +                                                                             
                " price "+                                                                                
        " ) finalresult,t_product_storage ps,t_position pos"+                                                            
        " where"+                                                                                         
            " finalresult.product_id = ps.product_id and"+                                   
            " finalresult.poscode = ps.position_code and"+                                                
            " finalresult.prvcode = ps.provider_code and"+                                                
            " finalresult.balmode = ps.balance_mode and"+                                                 
            " finalresult.price = ps.product_price and"+                                                  
            " ps.warehouse_code = '"+this.warehouseCode+"'"+                      
            " and pos.warehouse_code = ps.warehouse_code"+
            " and pos.code = ps.position_code";                                        
        return this.executeUpdateSql(con, sql);
    }
    
    /*  结算入库主单  */
    private boolean balanceInHead(Connection con){
        String sql = 
        "update " +
            "t_in_head " +
        "set " +
            "balance_flag = '"+this.balanceFlag+"', " +
            "balance_user = '"+this.balanceUser+"', " +
            "balance_user_id = '"+this.balanceUserId+"', " +
            "balance_time = '"+this.timeEnd+"', " +
            "last_update_user_id = '"+this.lastUpdateUserId+"'," +
            "last_update_user = '"+this.lastUpdateUser+"'," +
            "last_update_time = (select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual) " +
        "where " +
            "status = '"+this.status+"' " +
            "and balance_flag = '0' " +
            "and in_time >= '"+this.timeStart+"' " +
            "and in_time <= '"+this.timeEnd+"' " +
            "and warehouse_code = '"+this.warehouseCode+"'";
        return this.executeUpdateSql(con, sql);
    }
    
    /*  结算出库主单  */
    private boolean balanceOutHead(Connection con){
        String sql = 
        "update " +
            "t_out_head " +
        "set " +
            "balance_flag = '"+this.balanceFlag+"', " +
            "balance_user = '"+this.balanceUser+"', " +
            "balance_user_id = '"+this.balanceUserId+"', " +
            "balance_time = '"+this.timeEnd+"', " +
            "last_update_user_id = '"+this.lastUpdateUserId+"'," +
            "last_update_user = '"+this.lastUpdateUser+"'," +
            "last_update_time = (select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual) " +
        "where " +
            "status = '"+this.status+"' " +
            "and balance_flag = '0' " +
            "and out_time >= '"+this.timeStart+"' " +
            "and out_time <= '"+this.timeEnd+"' " +
            "and warehouse_code = '"+this.warehouseCode+"'";
        return this.executeUpdateSql(con, sql);
    }

    /*  执行更新SQL语句   */
    public boolean executeUpdateSql(Connection con,String sql) {
        PreparedStatement ps = null;
        if(con == null)
            return false;
        if(sql == null || sql == "")
            return false;
        try {
            ps = con.prepareStatement(sql);
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            
            e.printStackTrace();
            return false;
        }finally{
            try {
                if(ps != null)
                    ps.close();
            } catch (SQLException e) {
                
                e.printStackTrace();
            }
        }
    }

    /**
     * @return the isRunning
     */
    public static boolean isRunning() {
        return MonthStorageHis.isRunning;
    }
    
    /**
     * 获得仓库的最后一个月结月 YYYY-DD
     * */
    public static String getLastBalanceMonth(String warehouseCode){
        if(warehouseCode == null || warehouseCode.equals(""))
            return null;
        String sql = "select max(month) from t_month_storage where warehouse_code = '"+warehouseCode+"'";
        DBConnection dcon = null; 
        Connection ccon = null; 
        try {
            dcon = DBConnection.open(false);
            ccon = dcon.getConnection();
            ccon.setAutoCommit(true);
        } catch (SQLException e2) {
            
            e2.printStackTrace();
        } catch (Exception e2) {
            
            e2.printStackTrace();
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        String month = null;
        try {
            ps = ccon.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                month = rs.getString(1);
            }
        } catch (SQLException e) {
            
            e.printStackTrace();
        }finally{
            try {
                if(rs != null){
                    rs.close();
                }
            } catch (SQLException e1) {
                
                e1.printStackTrace();
            }
            try {
                if(ps != null)
                    ps.close();
            } catch (SQLException e) {
                
                e.printStackTrace();
            }
            if(ccon != null){
                try {
                    ccon.close();
                } catch (SQLException e) {
                    
                    e.printStackTrace();
                }
            }
            if(dcon != null){
                try {
                    dcon.close();
                } catch (Exception e) {
                    
                    e.printStackTrace();
                }
            }
        }
        return month;
    }
    
    public static String getEndBalanceTime(String month){
        String value = "";
        value += month.substring(0,4);
        value += "-";
        value += month.substring(4);
        value += "-32";
        return value;
    }
}
